# 主程序 - 模块化的数据检测
import pandas as pd
from sqlalchemy import create_engine, text
import json
from datetime import datetime
# 将根目录纳入模块搜索路径，以便导入 config 和 inspections 模块
import os
import sys
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from config import create_db_engine, load_env_tbl_name, load_env_structure_setl
# 获取 表名 与 setl列的列名
SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME = load_env_tbl_name()
(SETL_SETL_ID, SETL_MDTRT_ID, SETL_PSN_NO, SETL_HSP_ID, SETL_SETL_TIME,
 SETL_GNR_C, SETL_GNR_B, SETL_IN_DATE_CHECK, SETL_OUT_DATE_CHECK, SETL_VALIDFLAG) = load_env_structure_setl()

from inspections import (
    # X 系列结构
    inspect_x01_table_exists,
    inspect_x02_column_exists,
    # inspect_x03_primary_key_uniqueness,
    inspect_x04_not_null,
    inspect_x05_column_type_mismatch,
    inspect_x06_extra_columns,
    # mdtrt
    inspect_a01_duplicate_mdtrt_ids,
    inspect_a02_invalid_hsp_ids,
    # inspect_a03_hsp_lv_consistency,
    # inspect_a04_certificate_consistency,
    # inspect_a05_person_name_consistency,
    # inspect_a06_gender_consistency,
    # inspect_a07_birth_date_consistency,
    # setl
    inspect_b01_duplicate_setl_ids,
    inspect_b02_duplicate_mdtrt_ids,
    inspect_b03_setl_id_not_in_mdtrt,
    inspect_b04_setl_time_consistency,
    # fee
    inspect_c01_setl_id_not_in_fee,
    inspect_c02_fee_time_before_adm_time,
    inspect_c03_fee_time_after_dsc_time,
    inspect_c04_fee_sum_c_vs_setl_gnr_c,
    inspect_c05_fee_sum_b_vs_setl_gnr_b,
    inspect_c06_fee_qp_vs_c,
    # dx
    inspect_d01_diagnosis_main_count,
    inspect_d02_setlid_not_in,
    inspect_d03_setlid_not_in_mdtrt,
    # tx
    inspect_e01_surgery_main_count,
    inspect_e02_tx_time_vs_in_time,
    inspect_e03_tx_time_vs_dsch_time
)
from inspections_primary import (
    persist_x_issues_to_struct_table,
    persist_x_issues_to_dirty_data
)

# 创建数据库引擎
engine = create_db_engine()

# 检查结果收集列表
check_results = []

# ============ 创建记录表 ============
# 判断是否有 dirty_data 表，用于存储各种脏数据。
with engine.connect() as connection:
    # 判断是否存在
    result = connection.execute(
        text("SELECT COUNT(*) FROM user_tables WHERE table_name = 'DIRTY_DATA'")
    )
    if result.scalar() == 0:
        # 创建 DIRTY_DATA 表，有 SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO 四个字段
        connection.execute(
            text("""
            CREATE TABLE DIRTY_DATA (
                SETL_ID VARCHAR2(50),
                INSPECT_ID VARCHAR2(10),
                INSPECT_NAME VARCHAR2(100),
                LV VARCHAR2(50),
                INFO VARCHAR2(2000)
            )
            """)
        )
    else:
        # truncate这个表
        connection.execute(
            text("""
            TRUNCATE TABLE DIRTY_DATA
            """)
        )

print('============ X：结构完整性检查 ============')
# 结构检查不依赖 setl_count，先给 0 占位
setl_count_placeholder = 0
inspect_x01_table_exists(engine, check_results, setl_count_placeholder)
inspect_x02_column_exists(engine, check_results, setl_count_placeholder)
# inspect_x03_primary_key_uniqueness(engine, check_results, setl_count_placeholder)
inspect_x04_not_null(engine, check_results, setl_count_placeholder)
inspect_x05_column_type_mismatch(engine, check_results, setl_count_placeholder)
inspect_x06_extra_columns(engine, check_results, setl_count_placeholder)
print('============ X：结构完整性检查 FINISHED ============')

# ============ 执行各种检测 ============
print('============ 开始数据检测 ============')
# 查询setl表的setl_id的数量
with engine.connect() as connection:
    result = connection.execute(text(f"SELECT COUNT(DISTINCT {SETL_SETL_ID}) FROM {SETL_TBL_NAME}"))
    setl_count = result.scalar()
    print(f"结算表中的 SETL_ID 数量: {setl_count}")

# 执行 A 系列检测（就诊表相关）
print('============ A：测试就诊表 MDTRT_ID ============')
inspect_a01_duplicate_mdtrt_ids(engine, check_results, setl_count)
inspect_a02_invalid_hsp_ids(engine, check_results, setl_count)
# inspect_a03_hsp_lv_consistency(engine, check_results, setl_count)
# inspect_a04_certificate_consistency(engine, check_results, setl_count)
# inspect_a05_person_name_consistency(engine, check_results, setl_count)
# inspect_a06_gender_consistency(engine, check_results, setl_count)
# inspect_a07_birth_date_consistency(engine, check_results, setl_count)
print('============ A：测试就诊表 MDTRT_ID FINISHED ============')

# 执行 B 系列检测（结算表相关）
print('============ B：测试结算表 SETL_ID ============')
inspect_b01_duplicate_setl_ids(engine, check_results, setl_count)
inspect_b02_duplicate_mdtrt_ids(engine, check_results, setl_count)
inspect_b03_setl_id_not_in_mdtrt(engine, check_results, setl_count)
inspect_b04_setl_time_consistency(engine, check_results, setl_count)
print('============ B：测试结算表 SETL_ID FINISHED ============')

# 执行 C 系列检测（费用表相关）
print('============ C：测试费用表 FEE ============')
inspect_c01_setl_id_not_in_fee(engine, check_results, setl_count)
# inspect_c02_fee_time_before_adm_time(engine, check_results, setl_count)
# inspect_c03_fee_time_after_dsc_time(engine, check_results, setl_count)
# inspect_c04_fee_sum_c_vs_setl_gnr_c(engine, check_results, setl_count)
# inspect_c05_fee_sum_b_vs_setl_gnr_b(engine, check_results, setl_count)
# inspect_c06_fee_qp_vs_c(engine, check_results, setl_count)
print('============ C：测试费用表 FEE FINISHED ============')

# 执行 D 系列检测（诊断表相关）
print('============ D：测试诊断表 DX ============')
inspect_d01_diagnosis_main_count(engine, check_results, setl_count)
inspect_d02_setlid_not_in(engine, check_results, setl_count)
# inspect_d03_setlid_not_in_mdtrt(engine, check_results, setl_count)
print('============ D：测试诊断表 DX FINISHED ============')

# 执行 E 系列检测（手术表相关）
print('============ E：测试手术表 TX ============')
inspect_e01_surgery_main_count(engine, check_results, setl_count)
# inspect_e02_tx_time_vs_in_time(engine, check_results, setl_count)
# inspect_e03_tx_time_vs_dsch_time(engine, check_results, setl_count)
print('============ E：测试手术表 TX FINISHED ============')

# 查询dirty_data表的setl_id的去重计数
dirty_count = 0
with engine.connect() as connection:
    result = connection.execute(text("SELECT COUNT(DISTINCT SETL_ID) FROM DIRTY_DATA"))
    dirty_count = result.scalar()
    print(f"DIRTY_DATA 表中的 SETL_ID 去重计数: {dirty_count}")
    print(f"占主单表总数量的比例：{dirty_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")

check_results.append({
    "inspect_id": "DIRTY_COUNT",
    "inspect_name": "DIRTY_DATA表SETL_ID去重计数",
    "sql": "SELECT COUNT(DISTINCT SETL_ID) FROM DIRTY_DATA",
    "msg": "DIRTY_DATA表SETL_ID去重计数",
    "info": [{"SETL_ID_COUNT": dirty_count}],
    "count": dirty_count,
    "ocp": dirty_count / setl_count * 100 if setl_count > 0 else 0
})

# 保存所有检测结果到JSON文件
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
json_filename = f"step4_inspect_result_{timestamp}.json"

# 将结果保存到一个excel文件
dfCheckResults = pd.DataFrame(check_results)
dfCheckResults.to_excel(f"step4_inspect_result_{timestamp}.xlsx", index=False)

with open(json_filename, "w", encoding="utf-8") as f:
    json.dump(check_results, f, ensure_ascii=False, indent=2)

print(f'检测结果已保存到 {json_filename}')
print(f'总共完成 {len(check_results)} 项检测')

# ====== 持久化结构问题 (增强5 & 增强6) ======
try:
    persist_x_issues_to_struct_table(engine, check_results)
    persist_x_issues_to_dirty_data(engine, check_results)
    print('结构问题已写入 STRUCT_ISSUE_LOG 与 DIRTY_DATA (LV=STRUCT)。')
except Exception as e:
    print(f'结构问题持久化失败: {e}')

print('============ 数据检测完成 ============')